﻿using FTBS.Models.Entities;
using FTBS.Models.Entities.Concert;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace FTBS.Models.DAO
{
    public class ScheduleDAO
    {
        private FilmTicketBookingSystemEntities _entities = new FilmTicketBookingSystemEntities();

        #region --- Booking Ticket ---

        /// <summary>
        /// Get list of schedule
        /// </summary>
        /// <returns>List of schedule</returns>
        /// <author>vuongvt</author>
        /// <date>2014/02/27</date>
        public IEnumerable<Schedule> GetListShedule()
        {
            try
            {
                IEnumerable<Schedule> listSchedule = new List<Schedule>();
                listSchedule = _entities.Schedules.ToList().Where(schedule => schedule.IsDeleted == false);
                return listSchedule;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get list of films are showing
        /// </summary>
        /// <returns></returns>
        /// <author>vuongvt</author>
        /// <date>2014/03/2</date>
        public List<Film> GetListOfFilmShowing()
        {
            try
            {
                List<Film> listFilm = new List<Film>();
                var listResult = _entities.Schedules.Where(s => s.IsDeleted == false)
                                                    .GroupBy(s => s.FilmId)
                                                    .Select(r => new
                                                    {
                                                        FilmId = r.Key
                                                    })
                                                    .Join(_entities.Films, s => s.FilmId, f => f.FilmId, (s, f) => new { s, f })
                                                    .Select(r => new
                                                    {
                                                        Film = r.f
                                                    });

                foreach (var item in listResult)
                {
                    listFilm.Add(item.Film);
                }
                return listFilm;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get list theater of film by filmId
        /// -> Use when user click to a film. Load all theater are showing for this film
        /// </summary>
        /// <param name="filmId"></param>
        /// <returns></returns>
        public List<Theater> GetListTheaterWhenChooseFilm(int filmId)
        {
            try
            {
                var listTheater = new List<Theater>();

                var resultSearch = _entities.Schedules.Where(s => s.FilmId == filmId && s.IsDeleted == false)
                                                      .GroupBy(s => s.TheaterId)
                                                      .Select(r => new
                                                      {
                                                          TheaterId = r.Key
                                                      })
                                                      .Join(_entities.Theaters, sch => sch.TheaterId, th => th.TheaterId, (sch, th) => new { sch, th })
                                                      .Select(r => new
                                                      {
                                                          Theater = r.th
                                                      });
                foreach (var item in resultSearch)
                {
                    listTheater.Add(item.Theater);
                }
                var resultOfSearchs = listTheater.GroupBy(t => t.TheaterId);
                return listTheater;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get list showtime by showtimeid in schedule table
        /// </summary>
        /// <param name="showtimeId"></param>
        /// <returns></returns>
        public List<ShowTime> GetListShowTimeWhenChooseTheater(int theaterId, int filmId)
        {
            try
            {
                var listShowTime = new List<ShowTime>();

                var resultSearch = _entities.Schedules.Where(schedule => schedule.TheaterId == theaterId && schedule.IsDeleted == false && schedule.FilmId == filmId)
                                                      .GroupBy(schedule => schedule.ShowTimeId)
                                                      .Select(result => new
                                                      {
                                                          ShowTimeId = result.Key
                                                      })
                                                      .Join(_entities.ShowTimes, schedule => schedule.ShowTimeId, showtime => showtime.ShowTimeId, (schedule, showtime) => new { schedule, showtime })
                                                      .Where(scheduleshowtime => scheduleshowtime.showtime.IsActive == true)
                                                      .Select(finalResult => new
                                                      {
                                                          ShowTime = finalResult.showtime
                                                      });

                foreach (var item in resultSearch)
                {
                    listShowTime.Add(item.ShowTime);
                }

                return listShowTime;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get RoomId to load chair for booking
        /// </summary>
        /// <param name="searchSchedule"></param>
        /// <returns>RoomId</returns>
        /// <author>vuongvt</author>
        /// <date>2014/03/02</date>
        public int GetRoomIdForBooking(SearchSchedule searchSchedule)
        {
            try
            {
                var resultQuery = _entities.Schedules.Where(s => s.FilmId == searchSchedule.FilmId
                                                             && s.ShowTimeId == searchSchedule.ShowTimeId
                                                             && s.TheaterId == searchSchedule.TheaterId)
                                                 .Select(r => new
                                                 {
                                                     RoomId = r.RoomId
                                                 });
                return resultQuery.FirstOrDefault().RoomId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get ScheduleId base on FilmId, RoomId, ShowTimeId, TheaterId
        /// </summary>
        /// <param name="filmId"></param>
        /// <param name="roomId"></param>
        /// <param name="showtimeId"></param>
        /// <param name="theaterId"></param>
        /// <returns>ScheduleId</returns>
        /// <author>vuongvt</author>
        public int GetScheduleId(int filmId, int roomId, int showtimeId, int theaterId)
        {
            try
            {
                var resultQuery = _entities.Schedules.Where(s => s.FilmId == filmId
                                                             && s.RoomId == roomId
                                                             && s.ShowTimeId == showtimeId
                                                             && s.TheaterId == theaterId).FirstOrDefault();
                return resultQuery.ScheduleId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get user's information by username
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public User GetUserInfoByUserName(string userName)
        {
            try
            {
                var resultQuery = (from user in _entities.Users
                                   where user.UserName == userName
                                         && user.IsActive == true
                                         && user.IsDeleted == false
                                   select user).FirstOrDefault();

                return resultQuery;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        #region "Manage Schedule"

        /// <summary>
        /// Get all schedule information to load to list schedule
        /// </summary>
        /// <returns></returns>
        public List<ScheduleConcertEntity> GetAllScheduleInfo()
        {
            try
            {
                List<ScheduleConcertEntity> listResult = new List<ScheduleConcertEntity>();

                var resultQuery = from schedule in _entities.Schedules
                                  join film in _entities.Films on schedule.FilmId equals film.FilmId
                                  join room in _entities.Rooms on schedule.RoomId equals room.RoomId
                                  join theater in _entities.Theaters on schedule.TheaterId equals theater.TheaterId
                                  join showtime in _entities.ShowTimes on schedule.ShowTimeId equals showtime.ShowTimeId
                                  join promotion in _entities.Promotions on schedule.PromotionId equals promotion.PromotionId
                                  join user in _entities.Users on schedule.UserName equals user.UserName
                                  where schedule.IsDeleted == false
                                  && film.IsDeleted == false
                                  && room.IsDeleted == false
                                  && theater.IsDeleted == false
                                  && showtime.IsDeleted == false
                                  && showtime.IsActive == true
                                  && promotion.IsDeleted == false
                                  && user.IsActive == true
                                  && user.IsDeleted == false
                                  orderby showtime.Date descending
                                  select new
                                  {
                                      Schedules = schedule,
                                      FilmName = film.Name,
                                      RoomName = room.RoomName,
                                      Theater = theater.TheaterName,
                                      DateForShow = showtime.Date,
                                      TimeForShow = showtime.Time,
                                      PromotionName = promotion.PromotionName,
                                      PromotionRate = promotion.Rate,
                                      PeopleCreate = user.UserName
                                  };

                foreach (var item in resultQuery.ToList())
                {
                    ScheduleConcertEntity scheduleEntity = new ScheduleConcertEntity();
                    scheduleEntity.ScheduleId = item.Schedules.ScheduleId;
                    scheduleEntity.FilmId = item.Schedules.FilmId;
                    scheduleEntity.RoomId = item.Schedules.RoomId;
                    scheduleEntity.ShowTimeId = item.Schedules.ShowTimeId;
                    scheduleEntity.TheaterId = item.Schedules.TheaterId;
                    scheduleEntity.PromotionId = item.Schedules.PromotionId;
                    scheduleEntity.UserName = item.Schedules.UserName;
                    scheduleEntity.FilmName = item.FilmName;
                    scheduleEntity.RoomName = item.RoomName;
                    scheduleEntity.TheaterName = item.Theater;
                    scheduleEntity.DateForShow = item.DateForShow.ToShortDateString();
                    scheduleEntity.TimeForShow = item.TimeForShow.ToString(@"hh\:mm");
                    scheduleEntity.PromotionName = item.PromotionName;
                    scheduleEntity.PromotionRate = Convert.ToDouble(item.PromotionRate);
                    scheduleEntity.PeopleCreate = item.PeopleCreate;
                    listResult.Add(scheduleEntity);
                }
                return listResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Add new a schedule into Schedule table
        /// </summary>
        /// <param name="scheduleConcertEntity"></param>
        /// <returns>1: Success, 0: Fail</returns>
        /// <author>vuongvt</author>
        /// <date>2014/03/04</date>
        public int CreateSchedule(Schedule schedule)
        {
            try
            {
                schedule.IsDeleted = false;
                schedule.UserName = "vuongvt";
                _entities.Schedules.Add(schedule);
                var resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get schedule by ScheduleId
        /// </summary>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        public Schedule GetScheduleById(int scheduleId)
        {
            try
            {
                var resultQuery = (from schedule in _entities.Schedules
                                   where schedule.ScheduleId == scheduleId && schedule.IsDeleted == false
                                   select schedule).FirstOrDefault();
                return resultQuery;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Edit a schedule
        /// </summary>
        /// <param name="scheduleConcertEntity"></param>
        /// <returns>1: Success, 0: Fail</returns>
        /// <author>vuongvt</author>
        /// <date>2014/03/04</date>
        public int EditSchedule(Schedule schedule)
        {
            try
            {
                _entities.Entry(schedule).State = System.Data.EntityState.Modified;
                var resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Delete a schedule
        /// </summary>
        /// <param name="scheduleConcertEntity"></param>
        /// <returns>1: Success, 0: Fail</returns>
        /// <author>vuongvt</author>
        /// <date>2014/03/04</date>
        public int DeleteSchedule(Schedule schedule)
        {
            try
            {
                schedule.IsDeleted = true;
                _entities.Entry(schedule).State = EntityState.Modified;
                var resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region --LoadFilmOnHomePage--

        /// <summary>
        /// LoadFilm in Comming soon
        /// </summary>
        /// <returns>ListFilm</returns>
        /// <author>nguyenht</author>
        /// <date>2014/03/06</date>
        public List<Film> LoadFilmsCommingSoon()
        {
            try
            {
                var _listFilm = new List<Film>();
                var resultQuery = _entities.Schedules.Where(s => s.ShowTime.Date >= DateTime.Today 
                                                            && s.ShowTime.IsActive == false)
                                                    .Select(r => new
                                                    {
                                                        Film = r.Film
                                                    });
                foreach (var item in resultQuery)
                {
                    _listFilm.Add(item.Film);
                }
                return _listFilm;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Load Phim Dang Chieu
        /// </summary>
        /// <returns>List film</returns>
        /// <author>nguyenht</author>
        public List<Film> LoadFilmsShowing()
        {
            try
            {
                var _listFilm = new List<Film>();
                var resultQuery = _entities.Schedules.Where(s => s.ShowTime.Date >= DateTime.Today)
                                                     .Where(s => s.ShowTime.IsActive == true)
                    .Select(r => new
                    {
                        Film = r.Film
                    });
                foreach (var item in resultQuery)
                {
                    _listFilm.Add(item.Film);
                }
                return _listFilm;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Load phim khuyen mai. Promotion active && Showtime active
        /// </summary>
        /// <returns></returns>
        public List<Film> LoadFilmsPromotion()
        {
            try
            {
                var _listFilm = new List<Film>();
                var resultQuery = _entities.Schedules
                                                     .Where(s => s.IsDeleted == false)
                                                     .Where(s => s.ShowTime.IsActive == true)
                                                     .Where(s => s.ShowTime.IsDeleted == false)
                                                     .Where(s => s.Promotion.IsDeleted == false)
                                                    .Select(r => new
                                                    {
                                                        Film = r.Film
                                                    });
                foreach (var item in resultQuery)
                {
                    _listFilm.Add(item.Film);
                }
                return _listFilm;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Max rate promotion in Schedule
        /// </summary>
        /// <returns></returns>
        public Film BestPromtion()
        {
            try
            {
                FilmConcertEntity filmBestPromotion = new FilmConcertEntity();
                List<Film> listFilmPromotion = new List<Film>();
                listFilmPromotion = LoadFilmsPromotion();

                Schedule scheduleBestPromotion = new Schedule();

                var resultQuery = (from schedule in _entities.Schedules
                                          where schedule.IsDeleted == false
                                                && schedule.ShowTime.IsActive == true
                                                && schedule.ShowTime.IsDeleted == false
                                                && schedule.Promotion.IsDeleted == false
                                          orderby schedule.Promotion.Rate
                                          select new {
                                              ScheduleDescription = schedule.Promotion.PromotionName,
                                              Film = schedule.Film
                                          }).First();
                if (resultQuery.Film != null && resultQuery.ScheduleDescription != null)
                {
                    filmBestPromotion.FilmId = resultQuery.Film.FilmId;
                    filmBestPromotion.Name = resultQuery.Film.Name;
                    filmBestPromotion.Length = resultQuery.Film.Length;
                    filmBestPromotion.Nation = resultQuery.Film.Nation;
                    filmBestPromotion.Director = resultQuery.Film.Director;
                    filmBestPromotion.Actor = resultQuery.Film.Actor;
                    filmBestPromotion.AgeLimit = resultQuery.Film.AgeLimit;
                    filmBestPromotion.FormatId = resultQuery.Film.FormatId;
                    filmBestPromotion.CategoryId = resultQuery.Film.CategoryId;
                    filmBestPromotion.Description = resultQuery.Film.Description;
                    filmBestPromotion.ImageURL = resultQuery.Film.ImageURL;
                    filmBestPromotion.IsDeleted = resultQuery.Film.IsDeleted;
                }
                
                return filmBestPromotion;
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        

        #endregion

        
    }
}